 package database;

import java.sql.*;
import java.util.ArrayList;

import model.Delivery;
import model.Estate;
import model.Invoice;
import oracle.jdbc.pool.OracleDataSource;
import test.Main;

public class EstateOperations 
{
	private Connection conn;
	private Statement stm;
	private ResultSet rst;
	private PreparedStatement prst;
	public EstateOperations()
	{
		conn=openDB();
	}
	// open database connection
	public Connection openDB()
	{
		try 
		{
			OracleDataSource ods = new OracleDataSource();

			if ( test.Main.dataConn == "GaryTallaght"){
				ods.setURL("jdbc:oracle:thin:@//10.10.2.7:1521/global1");
				ods.setUser("X00106491");
				ods.setPassword("db13May85");
			}
			else if ( test.Main.dataConn == "KeythTallaght"){
				ods.setURL("jdbc:oracle:thin:@//10.10.2.7:1521/global1");
				ods.setUser("X00106271");
				ods.setPassword("db19Jan82");
			}
			else if ( test.Main.dataConn == "KeythHome"){
				ods.setURL("jdbc:oracle:thin:HR/localhost:1521:XE");
				ods.setUser("System");
				ods.setPassword("7481600kW");
			}
			else if ( test.Main.dataConn == "GaryHome"){
				ods.setURL("jdbc:oracle:thin:HR/SYSTEM@localhost:1521:XE");
				ods.setUser("SYSTEM");
				ods.setPassword("1nf1n1ty");
			}

			conn = ods.getConnection();
			System.out.println("connected est.");
		} 
		catch (Exception e) 
		{
			System.out.print("Unable to load driver " + e);
			System.exit(1);
		}
		return conn;
	}
	// drop estate table from database
	public void dropEstateTable() 
	{
		System.out.println("Checking for existing tables.");
		try
		{
			stm = conn.createStatement();

			try 
			{
				// Dropping a sequence
				String dropseq = "drop sequence est_id_seq";
				stm = conn.prepareStatement(dropseq);
				stm.executeUpdate(dropseq);
			}
				catch(SQLSyntaxErrorException e)
				{
					
				}
			try
			{
			stm.execute("DROP TABLE orders");
			System.out.println("order table dropped.");
			}
			catch(SQLSyntaxErrorException e)
			{
				
			}
			try
			{
			stm.execute("DROP TABLE Customer");
			System.out.println("customer table dropped.");
			}
			catch(SQLSyntaxErrorException e)
			{
				
			}
			try
			{
			stm.execute("DROP TABLE Road");
			System.out.println("road table dropped.");
			}
			catch(SQLSyntaxErrorException e)
			{
				
			}
				stm.execute("DROP TABLE Estates1");
				System.out.println("Estate table dropped.");
			}  
		catch (SQLException ex) 
		{
			System.out.println("ERROR: " + ex.getMessage());
			//ex.printStackTrace();
		}
	}
	// create an estate table in database
	public void CreateEstateTable() 
	{
		try 
		{
			stm = conn.createStatement();
			String create = "CREATE TABLE Estates1"
					+ "(est_id NUMBER PRIMARY KEY, est_name VARCHAR(40), est_price number(6,2))";

			stm.executeUpdate(create);
			System.out.println("table created");
			// Creating a sequence
			String createseq = "create sequence est_id_seq increment by 1 start with 1 nocache";
			stm = conn.createStatement();
			stm.executeUpdate(createseq);
		} 
		catch (SQLException e) 
		{
			System.out.print("SQL Exception " + e);
			System.exit(1);
		}
	}
	
	// delete an estate from table
	public void deleteEstate(int i) 
	{
		try 
		{
			String cmd = "DELETE FROM Estates1 WHERE est_id =" + i;
			stm = conn.createStatement();
			stm.executeUpdate(cmd);
		} 
		catch (Exception e) 
		{
			System.out.println(e);
		}
	}
	// add an estate to the table
	public void addEstate(Estate e) 
	{
		try 
		{
			String sql = "INSERT INTO Estates1(est_id, est_name, est_price) VALUES (est_id_seq.nextVal,?,?)";

			prst = conn.prepareStatement(sql);
			prst.setString(1, e.getName());
			prst.setDouble(2, e.getPrice());

			prst.executeUpdate();
		} 
		catch (Exception se) 
		{
			System.out.println(se);
		}
	}
	// return a result set of all estates in table
	public ResultSet getEstate() 
	{
		try 
		{
			String queryString = "SELECT * FROM Estates1 ORDER BY est_id";

			stm = conn.createStatement();
			rst = stm.executeQuery(queryString);
		} 
		catch (Exception e) 
		{
			System.out.println(e);
		}
		return rst;
	}
	
	// return an array list of all estates names
		public ArrayList<String> getEstateNames() 
		{
			ArrayList<String> ests=new ArrayList<String>();
			try 
			{
				String queryString = "SELECT est_name FROM Estates1 order by est_id";

				stm = conn.createStatement();
				rst = stm.executeQuery(queryString);
				while(rst.next())
				{
					ests.add(rst.getString(1));
				}
			} 
			catch (Exception e) 
			{
				System.out.println(e);
			}
			return ests;
		}
	
		// return an array list of all orders per estate
		public ArrayList<Delivery> getEstateDeliveries(int e,int day) 
		{
			ArrayList<Delivery> ests=new ArrayList<Delivery>();
			Delivery d=null;
			try 
			{
				String queryString = "select c.cust_house,c.road_id,p.prod_name,"+
									"p.prod_supplier,oi.prod_qty,o.user_id "+
									"from customer c,orders o,day d,orderitem oi,product p "+
									"where oi.prod_id=p.prod_id "+
									"and oi.ord_num=d.ord_num "+
									"and oi.day_id=d.day_id "+
									"and d.ord_num=o.ord_num "+
									"and c.cust_id=o.cust_id "+
									"and oi.day_id="+day+
									" and c.road_id in (select road_id "+
									"from road where est_id="+e+") "+
									"order by c.road_id,c.cust_house";

				stm = conn.createStatement();
				rst = stm.executeQuery(queryString);
				while(rst.next())
				{
					if(rst.getInt(6)==Main.userId || Main.userId==1)
					{
						d=new Delivery(rst.getString(1),rst.getString(2),rst.getString(3),rst.getString(4),rst.getInt(5));
						ests.add(d);
					}
				}
			} 
			catch (Exception ee) 
			{
				System.out.println(ee);
			}
			return ests;
		}
		
		// return an array list of all amounts due
		public ArrayList<Invoice> getAllDue() 
		{
			ArrayList<Invoice> inv=new ArrayList<Invoice>();
			Invoice i=null;
			try 
			{
				String queryString ="select c.cust_house,r.est_id,r.road_id,sum(oi.prod_qty*p.prod_price)+e.est_price,o.user_id "+
						"from customer c,road r,orderitem oi,orders o,day d,product p,estates1 e "+
						"where oi.prod_id=p.prod_id "+
						"and oi.ord_num=d.ord_num "+
						"and oi.day_id=d.day_id "+
						"and d.ord_num=o.ord_num "+
						"and c.cust_id=o.cust_id "+
						"and r.road_id=c.road_id "+
						"and e.est_id=r.est_id "+
						"group by  c.cust_house,r.est_id,r.road_id,o.user_id,e.est_price "+
						"order by r.est_id,r.road_id,c.cust_house";

				stm = conn.createStatement();
				rst = stm.executeQuery(queryString);
				while(rst.next())
				{
					if(rst.getInt(5)==Main.userId || Main.userId==1)
					{
						i=new Invoice(rst.getString(1),rst.getInt(2),rst.getInt(3),rst.getDouble(4));
						inv.add(i);
					}
				}
			} 
			catch (Exception ee) 
			{
				System.out.println(ee);
			}
			return inv;
		}
		
	// update the price of an estate
	public void updateEstatePrice(int i,double p) 
	{
		try 
		{
			String sql = "UPDATE Estates1 SET est_price=" + p + "where est_id="
					+ i;

			stm = conn.createStatement();
			stm.executeUpdate(sql);
		} 
		catch (Exception e) 
		{
			System.out.println("Problem" + e);
		}
	}
	
	// return result set with estate name and id
	public ResultSet getEstIdName()
	{
		try 
		{
			String queryString = "SELECT est_name,est_id FROM Estates1 order by est_id";

			stm = conn.createStatement();
			rst = stm.executeQuery(queryString);
			
		} 
		catch (Exception e) 
		{
			System.out.println(e);
		}
		return rst;
	}
	// returning added estate
		public ResultSet getLastRow() 
		{
			String sqlStatement = "SELECT * FROM Estates1 ORDER BY est_id";
			try {
				prst = conn.prepareStatement(sqlStatement,
						ResultSet.TYPE_SCROLL_INSENSITIVE,
						ResultSet.CONCUR_READ_ONLY);
				rst = prst.executeQuery();
				rst.last();
				System.out.println(rst.getInt(1)+","+rst.getString(2)+","+rst.getDouble(3));
			} 
			catch (Exception ex) 
			{
				System.out.println("ERROR: " + ex.getMessage());
			}
			
			return rst;
		}
	
	// close the connection to database
	public void closeDB() 
	{
		try 
		{
			stm.close();
			rst.close();
			conn.close();
			System.out.print("Connection closed est");
		} 
		catch (SQLException e) 
		{
			System.out.print("Could not close connection ");
			e.printStackTrace();
		}
	}
}
